﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace DelonixRegia.Classes.Rooms
{
    public class RoomBookingDBManager
    {
        private const string connectionString = "Data Source=.; Initial Catalog=DelonixRegia; Integrated Security=True;";

        public static RoomBooking getBookingByRoomNo(int roomNo)
        {
            RoomBooking rb = null;

            try
            {
                SqlConnection conn = new SqlConnection(connectionString);
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "SELECT * FROM RoomBooking WHERE roomNo=@roomNo AND bookingID NOT IN (SELECT bookingID FROM RoomCheckout)";
                comm.Parameters.AddWithValue("@roomNo", roomNo);

                SqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    rb = new RoomBooking();
                    rb.bookingID = Convert.ToInt32(dr["bookingID"]);
                    rb.firstName = (string)dr["firstName"];
                    rb.lastName = (string)dr["lastName"];
                    rb.nric = dr["nric"].ToString().ToUpper();
                    rb.contactNo = (string)dr["contactNo"];
                    rb.emailAddress = (string)dr["emailAddress"];
                    rb.address = (string)dr["address"];
                    rb.noOfAdults = Convert.ToInt32(dr["noOfAdults"]);
                    rb.noOfChildren = Convert.ToInt32(dr["noOfChildren"]);
                    rb.checkIn = Convert.ToDateTime(dr["checkIn"]);
                    rb.checkOut = Convert.ToDateTime(dr["checkOut"]);
                    rb.paymentDetails = getPaymentDetail(Convert.ToInt32(dr["paymentDetails"]));
                    rb.roomNo = roomNo;
                }

                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }

            return rb;
        }

        public static RoomBooking getBookingByID(int id)
        {
            RoomBooking rb = null;

            try
            {
                SqlConnection conn = new SqlConnection(connectionString);
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "SELECT * FROM RoomBooking WHERE bookingID=@bookingID";
                comm.Parameters.AddWithValue("@bookingID", id);

                SqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    rb = new RoomBooking();
                    rb.bookingID = Convert.ToInt32(dr["bookingID"]);
                    rb.firstName = (string)dr["firstName"];
                    rb.lastName = (string)dr["lastName"];
                    rb.nric = dr["nric"].ToString().ToUpper();
                    rb.contactNo = (string)dr["contactNo"];
                    rb.emailAddress = (string)dr["emailAddress"];
                    rb.address = (string)dr["address"];
                    rb.noOfAdults = Convert.ToInt32(dr["noOfAdults"]);
                    rb.noOfChildren = Convert.ToInt32(dr["noOfChildren"]);
                    rb.checkIn = Convert.ToDateTime(dr["checkIn"]);
                    rb.checkOut = Convert.ToDateTime(dr["checkOut"]);
                    rb.paymentDetails = getPaymentDetail(Convert.ToInt32(dr["paymentDetails"]));
                    rb.roomNo = Convert.ToInt32(dr["roomNo"]);
                }

                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }

            return rb;
        }

        public static RoomBooking getBookingByNRIC(string nric)
        {
            RoomBooking rb = null;

            try
            {
                SqlConnection conn = new SqlConnection(connectionString);
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "SELECT * FROM RoomBooking WHERE nric=@nric AND bookingID NOT IN (SELECT bookingID FROM RoomCheckout)";
                comm.Parameters.AddWithValue("@nric", nric);

                SqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    rb = new RoomBooking();
                    rb.bookingID = Convert.ToInt32(dr["bookingID"]);
                    rb.firstName = (string)dr["firstName"];
                    rb.lastName = (string)dr["lastName"];
                    rb.nric = dr["nric"].ToString().ToUpper();
                    rb.contactNo = (string)dr["contactNo"];
                    rb.emailAddress = (string)dr["emailAddress"];
                    rb.address = (string)dr["address"];
                    rb.noOfAdults = Convert.ToInt32(dr["noOfAdults"]);
                    rb.noOfChildren = Convert.ToInt32(dr["noOfChildren"]);
                    rb.checkIn = Convert.ToDateTime(dr["checkIn"]);
                    rb.checkOut = Convert.ToDateTime(dr["checkOut"]);
                    rb.paymentDetails = getPaymentDetail(Convert.ToInt32(dr["paymentDetails"]));
                    rb.roomNo = Convert.ToInt32(dr["roomNo"]);
                }

                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }

            return rb;
        }

        public static PaymentDetail getPaymentDetail(int paymentID)
        {
            PaymentDetail pd = null;

            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = connectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "SELECT * FROM PaymentDetail WHERE paymentID=@paymentID";
                comm.Parameters.AddWithValue("@paymentID", paymentID);
                SqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    pd = new PaymentDetail();
                    pd.paymentID = paymentID;
                    pd.cardType = (string)dr["cardType"];
                    pd.cardholderName = (string)dr["cardholderName"];
                    pd.cardNo = (string)dr["cardNo"];
                    pd.expiryDate = (string)dr["expiryDate"];
                    pd.securityCode = Convert.ToInt32(dr["securityCode"]);
                }

                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }

            return pd;
        }

        public static bool Book(RoomBooking booking)
        {
            bool result = false;
            SqlConnection conn = new SqlConnection();
            SqlCommand comm = new SqlCommand();

            try
            {
                conn.ConnectionString = connectionString;
                conn.Open();
                SqlTransaction trans = conn.BeginTransaction();
                
                comm.Connection = conn;
                comm.Transaction = trans;

                comm.CommandText = "INSERT INTO PaymentDetail(cardType, cardholderName, cardNo, expiryDate, securityCode) VALUES (@type, @name, @no, @date, @code); SELECT SCOPE_IDENTITY();";
                comm.Parameters.AddWithValue("@type", booking.paymentDetails.cardType);
                comm.Parameters.AddWithValue("@name", booking.paymentDetails.cardholderName);
                comm.Parameters.AddWithValue("@no", booking.paymentDetails.cardNo);
                comm.Parameters.AddWithValue("@date", booking.paymentDetails.expiryDate);
                comm.Parameters.AddWithValue("@code", booking.paymentDetails.securityCode);

                object eS = comm.ExecuteScalar();
                int paymentDetailsID = 0;
                if (eS != DBNull.Value)
                {
                    paymentDetailsID = Convert.ToInt32(eS);
                }
                else
                {
                    comm.Transaction.Rollback();
                    conn.Close();
                    return false;
                }

                if (paymentDetailsID != 0)
                {
                    booking.paymentDetails.paymentID = paymentDetailsID;

                    comm.CommandText = "INSERT INTO RoomBooking(firstName, lastName, nric, contactNo, emailAddress, address, noOfAdults, noOfChildren, checkIn, checkOut, paymentDetails, roomNo) VALUES (@firstName, @lastName, @nric, @contactNo, @emailAddress, @address, @noOfAdults, @noOfChildren, @checkIn, @checkOut, @paymentDetails, @roomNo);";
                    comm.Parameters.AddWithValue("@firstName", booking.firstName);
                    comm.Parameters.AddWithValue("@lastName", booking.lastName);
                    comm.Parameters.AddWithValue("@nric", booking.nric);
                    comm.Parameters.AddWithValue("@contactNo", booking.contactNo);
                    comm.Parameters.AddWithValue("@emailAddress", booking.emailAddress);
                    comm.Parameters.AddWithValue("@address", booking.address);
                    comm.Parameters.AddWithValue("@noOfAdults", booking.noOfAdults);
                    comm.Parameters.AddWithValue("@noOfChildren", booking.noOfChildren);
                    comm.Parameters.AddWithValue("@checkIn", booking.checkIn);
                    comm.Parameters.AddWithValue("@checkOut", booking.checkOut);
                    comm.Parameters.AddWithValue("@paymentDetails", booking.paymentDetails.paymentID);
                    comm.Parameters.AddWithValue("@roomNo", booking.roomNo);

                    int rowsCreated = (int)comm.ExecuteNonQuery();
                    if (rowsCreated == 1)
                    {
                        result = true;
                        comm.Transaction.Commit();
                    }
                    else
                    {
                        comm.Transaction.Rollback();
                        conn.Close();
                        return false;
                    }
                }
                else
                {
                    comm.Transaction.Rollback();
                    conn.Close();
                    return false;
                }

                conn.Close();
            }
            catch (SqlException e)
            {
                comm.Transaction.Rollback();
                conn.Close();
                throw e;
            }

            return result;
        }
    }
}